﻿<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="sql.aspx.cs" Inherits="Jxym.wlkc2011.AdminManages.sql" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>执行SQL语句</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <style type="text/css">
        div { margin-bottom: 10px; }
        span { padding-right: 10px; }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <span>
            <asp:FileUpload ID="FileUpload1" runat="server" /></span><span>
                <asp:Button ID="Button2" runat="server" Text="打开" OnClick="Button2_Click" />
            </span><span>
                <asp:Label ID="Label2" runat="server" Text=""></asp:Label>
            </span>
    </div>
    <div>
        <asp:TextBox ID="TextBox1" runat="server" Width="100%" Height="500" TextMode="MultiLine">CREATE PROCEDURE [dbo].[pager]

@tblName varchar(255), -- 表名

@strGetFields varchar(1000) = '*', -- 需要返回的列

@fldName varchar(255)='', -- 排序的字段名

@PageSize int = 10, -- 页尺寸

@PageIndex int = 1, -- 页码

@doCount bit = 0, -- 返回记录总数, 非0 值则返回

@OrderType bit = 0, -- 设置排序类型, 非0 值则降序

@strWhere varchar(1500) = '''' -- 查询条件(注意: 不要加where) 

AS

declare @strSQL varchar(5000) -- 主语句

declare @strTmp varchar(110) -- 临时变量

declare @strOrder varchar(400) -- 排序类型


if @doCount != 0 

begin 

if @strWhere !='''' 

set @strSQL = 'select count(*) as Total from'+ ' '+ @tblName + ' where' +@strWhere
   
else set @strSQL = 'select count(*) as Total from ' + @tblName 

end 

--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况：

else 

begin 

if @OrderType != 0 

begin 

set @strTmp = '<(select min' 

set @strOrder = ' order by '+ @fldName +' desc' 

--如果@OrderType不是0，就执行降序，这句很重要！

end 

else 

begin 

set @strTmp = '>(select max' 

set @strOrder = ' order by ' + @fldName +' asc' 

end

if @PageIndex = 1 

begin  

if @strWhere != '''' 

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' 

from ' + @tblName + ' where ' + @strWhere + ' ' + @strOrder 

else

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' 

from '+ @tblName + ' '+ @strOrder 

--如果是第一页就执行以上代码，这样会加快执行速度

end

else 

begin 

--以下代码赋予了@strSQL以真正执行的SQL代码

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ' + @tblName + ' where ' + @fldName + '' + @strTmp + '('+ @fldName + ') from (select top ' + str((@PageIndex-1)*@PageSize) + ' '+ @fldName + ' from ' + @tblName + '' + @strOrder + ') as tblTmp)'+ @strOrder 

if @strWhere != '''' 

set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 

end 

end 

exec (@strSQL);</asp:TextBox>
    </div>
    <div>
        <asp:Button ID="Button1" runat="server" Text="执行" OnClick="Button1_Click" />
    </div>
    <div>
        <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    </div>
    </form>
</body>
</html>
